package blancosMikel.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;

import blancosMikel.controller.beans.subcategoriasBeans;

public class subcategoriasDAO extends SigaabmDAO{

StringBuilder query = new StringBuilder();
	
	public LinkedList<String> nombreSubcategorias(){
		LinkedList<String> lista = new LinkedList<>();
		try {
			conn = this.getConnectionMYSQL();
			query.append("SELECT nombre FROM ").append("subcategorias").append(";");
			conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			while (rs.next()){
				lista.add(rs.getString("nombre"));
			}
		} catch (SQLException sql) {
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
		return lista;	
	}
	
	public ArrayList<subcategoriasBeans> consultaTodoEnCategorias(){
		ArrayList<subcategoriasBeans> lista = new ArrayList<subcategoriasBeans>();
		subcategoriasBeans bean;
		try {
			conn = this.getConnectionMYSQL();
			query.append("SELECT * FROM ").append("subcategorias").append(";");
			conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			while (rs.next()){
				bean = new subcategoriasBeans();
				bean.setId_subcategorias(rs.getInt("id_subcategorias"));
				bean.setId_categorias(rs.getInt("id_categorias"));
				bean.setNombre(rs.getString("nombre"));
				bean.setUrl(rs.getString("url"));
				lista.add(bean);
			}
		} catch (SQLException sql) {
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
		return lista;	
	}
	
	public subcategoriasBeans consultaCategoria(int id){
		subcategoriasBeans bean = null;
		try {
			conn = this.getConnectionMYSQL();
			query.append("SELECT * FROM ").append("subcategorias")
			.append(" WHERE id_subcategorias =").append(id).append(";");
			conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			if(rs.next()){
				bean = new subcategoriasBeans();
				bean.setId_subcategorias(rs.getInt("id_subcategorias"));
				bean.setId_categorias(rs.getInt("id_categorias"));
				bean.setNombre(rs.getString("nombre"));
				bean.setUrl(rs.getString("url"));
			}
		} catch (SQLException sql) {
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
		return bean;
	}
	
	public void eliminarSubcategoria(int id){
		try {
			conn = this.getConnectionMYSQL();
			query.append("DELETE FROM ").append("subcategorias")
			.append(" WHERE id_subcategorias =").append(id).append(";");
			conn.prepareStatement(query.toString());
			ps.executeUpdate();
	    }catch (SQLException sql) {	    	
	    } finally {
			close(conn);
			close(ps);
			close(rs);
		}
	}
	
	public void insertaCategoria(subcategoriasBeans bean){
		try {
			conn = this.getConnectionMYSQL();
			query.append("INSERT INTO ").append("sucategorias")
			.append(" VALUES(").append(bean.getId_subcategorias())
			.append(",").append(bean.getId_categorias())
			.append(",'").append(bean.getNombre()).append("'")
			.append(",'").append(bean.getUrl()).append("'")
			.append(")").append(";");
			conn.prepareStatement(query.toString());
			ps.executeUpdate();
	    }catch (SQLException sql) {	    	
	    } finally {
			close(conn);
			close(ps);
			close(rs);
		}
	}
}
